//
//  DataBaseHandle.m
//  旅游咨询
//
//  Created by Letme on 15-4-20.
//  Copyright (c) 2015年 xubin. All rights reserved.
//

#import "DataBaseHandle.h"

@implementation DataBaseHandle
+ (instancetype)shareDataBaseHandel
{
    static DataBaseHandle *dataBaseHandel = nil;
    if (dataBaseHandel == nil) {
        dataBaseHandel = [[DataBaseHandle alloc] init];
    }
    return dataBaseHandel;
}
static sqlite3 *db = nil;

- (void)openDB
{
    if (db != nil) {
        return;
    }
    NSArray *array = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentPath = [array lastObject];
    NSString *sqliteFilePath = [documentPath stringByAppendingPathComponent:@"Foregin.sqlite"];
    NSLog(@"数据库地址:%@", sqliteFilePath);
    BOOL result = sqlite3_open(sqliteFilePath.UTF8String, &db);
    if (result == SQLITE_OK) {
        NSLog(@"数据库打开成功");
    }
}
- (void)createForegin
{
    NSString *createSql = @"CREATE TABLE IF NOT EXISTS foregin(number INTEGER PRIMARY KEY AUTOINCREMENT, country TEXT, image_Url)";
    BOOL result = sqlite3_exec(db, createSql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"创建表成功");
    }else{
        NSLog(@"创建表失败");
    }
}
- (void)insertForegin:(HomePageModel *)myModel
{
    NSString *insertSql = [NSString stringWithFormat:@"INSERT INTO foregin(country, image_Url) VALUES('%@', '%@')", myModel.name, myModel.imageUrl];
    int result = sqlite3_exec(db, insertSql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"插入成功");
    }else{
        NSLog(@"插入失败");
    }
}
- (NSMutableArray *)selectAllForeginModel
{
    NSString *selectSql = @"SELECT *FROM foregin";
    sqlite3_stmt *stmt = nil;
    BOOL result = sqlite3_prepare_v2(db, selectSql.UTF8String, -1, &stmt, nil);
    if (result == SQLITE_OK) {
        NSLog(@"查询准备成功");
        NSMutableArray *array = [NSMutableArray array];
        
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            const unsigned char *name = sqlite3_column_text(stmt, 1);
            const unsigned char *imageUrl = sqlite3_column_text(stmt, 2);
            NSString *nameStr = [NSString stringWithUTF8String:(const char *)name];
            NSString *imageUrlStr = [NSString stringWithUTF8String:(const char *)imageUrl];
            HomePageModel *myModel = [[HomePageModel alloc] init];
            myModel.name = nameStr;
            myModel.imageUrl = imageUrlStr;
            [array addObject:myModel];
            [myModel release];
        }
        return  array;
    }else{
        NSLog(@"查询失败");
    }
    return nil;
}
- (NSMutableArray *)selectAllEuropName
{
    NSString *selectSql = @"SELECT * FROM foregin";
    sqlite3_stmt *stmt = nil;
    BOOL result = sqlite3_prepare_v2(db, selectSql.UTF8String, -1, &stmt, nil);
    if (result == SQLITE_OK) {
        NSLog(@"查询准备成功");
        NSMutableArray *array = [NSMutableArray array];
        
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            //int number = sqlite3_column_int(stmt, 0);
            const unsigned char *name = sqlite3_column_text(stmt, 1);
            NSString *nameStr = [NSString stringWithUTF8String:(const char *)name];
            [array addObject:nameStr];
        }
        return  array;
    }else{
        NSLog(@"查询失败");
    }
    return nil;

}
- (void)dropAllErop
{
    NSString *dropSql = @"DROP TABLE foregin";
    BOOL result = sqlite3_exec(db, dropSql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"国外表删除成功");
    }else{
        NSLog(@"国外表删除失败");
    }
}









//2
- (void)createAsia
{
    NSString *createSql = @"CREATE TABLE IF NOT EXISTS asia(number INTEGER PRIMARY KEY AUTOINCREMENT, country TEXT, image_Url)";
    BOOL result = sqlite3_exec(db, createSql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"创建表成功");
    }else{
        NSLog(@"创建表失败");
    }
}
- (void)insertAsia:(HomePageModel *)myModel
{
    NSString *insertSql = [NSString stringWithFormat:@"INSERT INTO asia(country, image_Url) VALUES('%@', '%@')", myModel.name, myModel.imageUrl];
    int result = sqlite3_exec(db, insertSql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"插入成功");
    }else{
        NSLog(@"插入失败");
    }
}
- (NSMutableArray *)selectAllAsiaModel
{
    NSString *selectSql = @"SELECT *FROM asia";
    sqlite3_stmt *stmt = nil;
    BOOL result = sqlite3_prepare_v2(db, selectSql.UTF8String, -1, &stmt, nil);
    if (result == SQLITE_OK) {
        NSLog(@"查询准备成功");
        NSMutableArray *array = [NSMutableArray array];
        
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            const unsigned char *name = sqlite3_column_text(stmt, 1);
            const unsigned char *imageUrl = sqlite3_column_text(stmt, 2);
            NSString *nameStr = [NSString stringWithUTF8String:(const char *)name];
            NSString *imageUrlStr = [NSString stringWithUTF8String:(const char *)imageUrl];
            HomePageModel *myModel = [[HomePageModel alloc] init];
            myModel.name = nameStr;
            myModel.imageUrl = imageUrlStr;
            [array addObject:myModel];
            [myModel release];
        }
        return  array;
    }else{
        NSLog(@"查询失败");
    }
    return nil;
}
- (NSMutableArray *)selectAllAsiaName
{
    NSString *selectSql = @"SELECT * FROM asia";
    sqlite3_stmt *stmt = nil;
    BOOL result = sqlite3_prepare_v2(db, selectSql.UTF8String, -1, &stmt, nil);
    if (result == SQLITE_OK) {
        NSLog(@"查询准备成功");
        NSMutableArray *array = [NSMutableArray array];
        
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            //int number = sqlite3_column_int(stmt, 0);
            const unsigned char *name = sqlite3_column_text(stmt, 1);
            NSString *nameStr = [NSString stringWithUTF8String:(const char *)name];
            [array addObject:nameStr];
        }
        return  array;
    }else{
        NSLog(@"查询失败");
    }
    return nil;
    
}
- (void)dropAllAsia
{
    NSString *dropSql = @"DROP TABLE asia";
    BOOL result = sqlite3_exec(db, dropSql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"国外表删除成功");
    }else{
        NSLog(@"国外表删除失败");
    }
}
- (void)deleteCountryInAsian:(NSString *)nameStr
{
    NSString *deleteSql = [NSString stringWithFormat:@"DELETE FROM asia WHERE  country = '%@'", nameStr];
    int result = sqlite3_exec(db, deleteSql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"deleteCountryInAsian删除成功");
    }else{
        NSLog(@"deleteCountryInAsian删除失败");
    }
}






//3
- (void)createAmerican
{
    NSString *createSql = @"CREATE TABLE IF NOT EXISTS american(number INTEGER PRIMARY KEY AUTOINCREMENT, country TEXT, image_Url)";
    BOOL result = sqlite3_exec(db, createSql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"美洲创建表成功");
    }else{
        NSLog(@"美洲创建表失败");
    }
}
- (void)insertAmerican:(HomePageModel *)myModel
{
    NSString *insertSql = [NSString stringWithFormat:@"INSERT INTO american(country, image_Url) VALUES('%@', '%@')", myModel.name, myModel.imageUrl];
    int result = sqlite3_exec(db, insertSql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"美洲插入成功");
    }else{
        NSLog(@"美洲插入失败");
    }
}
- (NSMutableArray *)selectAllAmericanModel
{
    NSString *selectSql = @"SELECT *FROM american";
    sqlite3_stmt *stmt = nil;
    BOOL result = sqlite3_prepare_v2(db, selectSql.UTF8String, -1, &stmt, nil);
    if (result == SQLITE_OK) {
        NSLog(@"美洲查询准备成功");
        NSMutableArray *array = [NSMutableArray array];
        
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            const unsigned char *name = sqlite3_column_text(stmt, 1);
            const unsigned char *imageUrl = sqlite3_column_text(stmt, 2);
            NSString *nameStr = [NSString stringWithUTF8String:(const char *)name];
            NSString *imageUrlStr = [NSString stringWithUTF8String:(const char *)imageUrl];
            HomePageModel *myModel = [[HomePageModel alloc] init];
            myModel.name = nameStr;
            myModel.imageUrl = imageUrlStr;
            [array addObject:myModel];
            [myModel release];
        }
        return  array;
    }else{
        NSLog(@"美洲查询失败");
    }
    return nil;
}
- (NSMutableArray *)selectAllAmericanName
{
    NSString *selectSql = @"SELECT * FROM american";
    sqlite3_stmt *stmt = nil;
    BOOL result = sqlite3_prepare_v2(db, selectSql.UTF8String, -1, &stmt, nil);
    if (result == SQLITE_OK) {
        NSLog(@"美洲查询准备成功");
        NSMutableArray *array = [NSMutableArray array];
        
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            //int number = sqlite3_column_int(stmt, 0);
            const unsigned char *name = sqlite3_column_text(stmt, 1);
            NSString *nameStr = [NSString stringWithUTF8String:(const char *)name];
            [array addObject:nameStr];
        }
        return  array;
    }else{
        NSLog(@"美洲查询失败");
    }
    return nil;
    
}
- (void)dropAllAmerican
{
    NSString *dropSql = @"DROP TABLE american";
    BOOL result = sqlite3_exec(db, dropSql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"美洲表删除成功");
    }else{
        NSLog(@"美洲表删除失败");
    }
}


@end
